#!/usr/bin/python
# -*- coding: utf-8 -*-
from odbc import ODBC, CSV_SPLIT, SyncException
from util.logger import process_log, error_log, warn_log as warn
from util.tools import calculate_date_day
import os

if os.name == 'posix':
    import pymssql as mssql
elif os.name == 'nt':
    import pypyodbc as mssql
else:
    warn().warning('MSSQL not support %s' % os.name)


class MSSQL(ODBC):
    sys_db = ['master', 'tempdb', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB', 'FMW91TEST']

    def text_convert(self, table, tb_id, field, field_id, delta, fmt):
        max_value = self.read_index(tb_id, field_id)
        max_value = calculate_date_day(max_value, fmt, delta)
        if not max_value:
            return ''
        where = "where convert(varchar(120), [%s].[%s], 120) >= '%s'" % (table, field, max_value)
        return where

    def dump(self, database_name, table_name, fields_name, where, tb_id, return_cursor=False, breakpoint=-1):
        fields_with_table = map(lambda x: self.field_join(x[0], x[1]), [(table_name, field) for field in fields_name])
        fields_sql = ', '.join(fields_with_table)
        sql = "select %s from [%s].[dbo].[%s] %s " % (fields_sql, database_name, table_name, where)
        cursor = self.cursor
        process_log().info("Fetch data by sql: %s" % sql)

        if return_cursor:
            cursor.execute(sql)
            return cursor

        return self._sql_dump_csv(sql, tb_id, breakpoint)

    def count(self, database, table, where):
        """
            Get the count of sync by giving info
        Args:
            database (str):
            table (str):
            where (str):

        Returns:
            sql_count (int):
        """
        sql = 'select count(*) from [%s].[dbo].[%s] %s' % (database, table, where)
        process_log().info('Fetch rows count by sql statement: %s' % sql)
        self.cursor.execute(sql)
        res = self.cursor.fetchall()
        return str(res[0][0]) if res else '0'

    def max_value(self, database, table, index, where):
        sql = "select max(convert(varchar(120), [%s].[%s], 120)) " \
              "from [%s].[dbo].[%s] %s" % (table, index, database, table, where)
        process_log().info('Fetch max value by sql: %s' % sql)
        self.cursor.execute(sql)
        res = self.cursor.fetchall()
        return str(res[0][0]) if res else ''

    def field_join(self, table, field):
        raw_type = self.field_dict.get(table, {}).get(field, {}).get('raw_type', '').lower()
        if raw_type == 'timestamp':
            return u'%s_cast=cast([%s].[%s] as bigint)' % (field, table, field)
        elif raw_type in ('datetime2', 'datetime', 'decimal'):
            return u'convert(NVARCHAR(120), [%s].[%s], 120)' % (table, field)
        elif raw_type == 'date':
            return u'convert(NVARCHAR(120), [%s].[%s], 120)+\' 00:00:00\'' % (table, field)
        else:
            return u'[%s].[%s]' % (table, field)

    def fetch_schema(self, database, table_name):
        if not (database and table_name):
            process_log().error('table name and database is required!')
            return
        sql_use = '''use [%s]''' % database
        self.cursor.execute(sql_use)
        sql = '''
           SELECT DISTINCT (T1.TABLE_NAME), T1.COLUMN_NAME, T1.DATA_TYPE, T2.PRI  FROM
            (SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
              FROM INFORMATION_SCHEMA.COLUMNS
              WHERE (TABLE_NAME = '%s')) T1
            LEFT JOIN

            (SELECT COLUMN_NAME, 'PRI' AS PRI
              from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
              where TABLE_NAME= '%s') T2
            ON T2.COLUMN_NAME = T1.COLUMN_NAME''' % (table_name, table_name)
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    def __init__(self, connect_info):
        ODBC.__init__(self, connect_info)
        if os.name == 'posix':
            self.cursor = mssql.connect(host=connect_info.get('server', ''),
                                        port=connect_info.get('port', ''),
                                        user=connect_info.get('uid', ''),
                                        password=connect_info.get('pwd', ''),
                                        database=connect_info.get('database', '')).cursor()
        elif os.name == 'nt':
            connect_str_win_auth = r'Driver={SQL Native Client};Server=%(server)s;Trusted_Connection=yes;' % connect_info
            connect_str = r'Driver={SQL Server};Server=%(server)s,%(port)s;uid=%(uid)s;pwd=%(pwd)s;' % connect_info
            connect_str_odbc = r'DSN=%(server)s;uid=%(uid)s;pwd=%(pwd)s;' % connect_info

            db_type = connect_info.get('type', None)
            if db_type == 'ODBC':
                self.cursor = mssql.connect(connect_str_odbc, unicode_results=True).cursor()
            else:
                try:
                    self.cursor = mssql.connect(connect_str, unicode_results=True).cursor()
                except Exception, e:
                    warn().warning("Connect failed, auth: %s, try Windows Auth. reason:%s" %
                                   (connect_str, str(e).decode('unicode_escape')))
                    try:
                        self.cursor = mssql.connect(connect_str_win_auth, unicode_results=True).cursor()
                    except Exception, e:
                        error_log().error('Connect faild, auth: %s, reason:%s' %
                                          (connect_str_win_auth, str(e).decode('unicode_escape')))
                        raise e

    def fetch_databases(self):
        sql = "SELECT name FROM [master].[dbo].[sysdatabases]"
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    def fetch_tables(self, database, view=True):
        sql = "USE [%s]" % database
        self.cursor.execute(sql)
        sql = "SELECT DISTINCT(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES " \
              "WHERE TABLE_CATALOG='%s' ORDER BY TABLE_NAME " % database
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    def fetch_fields(self, database, table):
        sql = 'USE [%s]' % database
        self.cursor.execute(sql)
        sql = "select distinct (COLUMN_NAME), DATA_TYPE " \
              "from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='%s'" % table
        try:
            self.cursor.execute(sql)
        except Exception, e:
            raise Exception('Exception when fetch fields. sql:%s, error:%s' % (sql, e))
        return self.cursor.fetchall()

    def fetch_record_count(self, database, table):
        sql = "select count(1) from [%s].[dbo].[%s]" % (database, table)
        self.cursor.execute(sql)
        return self.cursor.fetchall()
